package com.scs.application.modules.fsd.service.impl;

import com.alibaba.fastjson.JSON;
import com.scs.application.core.exception.BusinessException;
import com.scs.application.core.utils.PinYinUtils;
import com.scs.application.core.utils.StringUtils;
import com.scs.application.modules.base.service.PatientService;
import com.scs.application.modules.fsd.consts.SPDServiceUrl;
import com.scs.application.modules.fsd.entity.FsdLog;
import com.scs.application.modules.fsd.entity.FsdUser;
import com.scs.application.modules.fsd.service.FsdLogService;
import com.scs.application.modules.fsd.service.SPDService;
import com.scs.application.modules.fsd.thirdparty.properties.SPDProperties;
import com.scs.application.modules.fsd.vo.*;
import com.scs.application.modules.sys.model.RestResult;
import com.scs.application.modules.sys.utils.UtilDatabase;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Primary;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import javax.annotation.Nullable;
import javax.annotation.Resource;
import java.sql.*;
import java.util.*;

/**
 */
@Service
@Primary
public class SPDServiceImpl implements SPDService {
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());
    String url;
    String driverClassName;
    String username;
    @Value("${res.real-path}")
    private String resPath;

    @Autowired
    PatientService patientService;

    @Autowired
    private SPDProperties spdProperties;

    @Autowired
    private RestTemplate restTemplate;

    @Autowired
    private FsdLogService fsdLogService;


    @Resource
    private JdbcTemplate jdbcTemplate;

    public List<StockResult> getStockBySql(String sql) {
        List<StockResult> stockResults = new ArrayList<>();
        List<String> uniquerfid = new ArrayList<>();
        ResultSet rs = null;
        try {
            Class.forName(driverClassName);
            Connection conn = DriverManager.getConnection(url, username, UtilDatabase.getPassword(username));
            Statement stmt = conn.createStatement();
            ResultSet hasResultSet = stmt.executeQuery(sql);
            if (hasResultSet != null) {
                rs = stmt.getResultSet();
                Integer id = 1;
                while (rs.next()) {
                    if(uniquerfid.contains(rs.getString("RFID")))
                    {
                        continue;
                    }else {
                        uniquerfid.add(rs.getString("RFID"));
                    }
                    StockResult stockResult = new StockResult();
                    stockResult.setId(id++);
                    stockResult.setMaterialCode(rs.getString("MaterialCode"));
                    stockResult.setMaterialName(rs.getString("MaterialName"));
                    stockResult.setPy(PinYinUtils.getAlpha(stockResult.getMaterialName()));
                    stockResult.setMaterialSpec(rs.getString("MaterialSpec"));
                    stockResult.setUnit(rs.getString("Unit"));
                    stockResult.setType(rs.getString("Type"));
                    stockResult.setPackageUnit(rs.getString("PackageUnit"));
                    stockResult.setPackageQty(rs.getInt("PackageQty"));
                    stockResult.setRfid(rs.getString("RFID"));
                    stockResult.setSn(rs.getString("SN"));
                    stockResult.setLot(rs.getString("Lot"));
                    stockResult.setExp(rs.getString("EXP"));
                    stockResult.setStockQty(rs.getInt("StockQty"));
                    stockResult.setLockQty(rs.getInt("LockQty"));
                    stockResult.setStorageCode(rs.getString("StorageCode")+"");
                    stockResult.setInactive(rs.getBoolean("Inactive"));
                    List<Map<String, String>> desc = new ArrayList<>();
                    Map<String, String> item = new HashMap<>();
                    item.put("label", "品名");
                    item.put("value", stockResult.getMaterialName()+(stockResult.getInactive()?"(已禁用)":stockResult.getStockQty()==0?"(已消耗)":stockResult.getLockQty()==1?"(已锁定"+(rs.getString("busLockRemark")!=null?rs.getString("busLockRemark"):"")+")":""));
                    desc.add(item);
                    item = new HashMap<>();
                    item.put("label", "品规");
                    item.put("value", stockResult.getMaterialSpec());
                    desc.add(item);
                    item = new HashMap<>();
                    item.put("label", "批号效期");
                    item.put("value", stockResult.getLot() + ";" + stockResult.getExp() + ";" + stockResult.getRfid()+ ";" + stockResult.getSn());
                    desc.add(item);
                    stockResult.setDesc(desc);
                    stockResults.add(stockResult);
                }
            }
            conn.close();

        } catch (Exception e) {

        }
        System.out.println(stockResults);
        return stockResults;
    }

    public List<StockResult> getStockBySqlSN(String sql) {
        List<StockResult> stockResults = new ArrayList<>();
        ResultSet rs = null;
        try {
            Class.forName(driverClassName);
            Connection conn = DriverManager.getConnection(url, username, UtilDatabase.getPassword(username));
            Statement stmt = conn.createStatement();
            ResultSet hasResultSet = stmt.executeQuery(sql);
            if (hasResultSet != null) {
                rs = stmt.getResultSet();
                Integer id = 1;
                while (rs.next()) {
                    StockResult stockResult = new StockResult();
                    stockResult.setId(id++);
                    stockResult.setMaterialCode(rs.getString("MaterialCode"));
                    stockResult.setMaterialName(rs.getString("MaterialName"));
                    stockResult.setPy(PinYinUtils.getAlpha(stockResult.getMaterialName()));
                    stockResult.setMaterialSpec(rs.getString("MaterialSpec"));
                    stockResult.setUnit(rs.getString("Unit"));
                    stockResult.setType(rs.getString("Type"));
                    stockResult.setPackageUnit(rs.getString("PackageUnit"));
                    stockResult.setPackageQty(rs.getInt("PackageQty"));
                    stockResult.setRfid(rs.getString("RFID"));
                    stockResult.setSn(rs.getString("SN"));
                    stockResult.setLot(rs.getString("Lot"));
                    stockResult.setExp(rs.getString("EXP"));
                    stockResult.setStockQty(rs.getInt("StockQty"));
                    stockResult.setLockQty(rs.getInt("LockQty"));
                    stockResult.setStorageCode(rs.getString("StorageCode")+"");
                    stockResult.setInactive(rs.getBoolean("Inactive"));
                    List<Map<String, String>> desc = new ArrayList<>();
                    Map<String, String> item = new HashMap<>();
                    item.put("label", "品名");
                    item.put("value", stockResult.getMaterialName()+(stockResult.getInactive()?"(已禁用)":stockResult.getStockQty()==0?"(已消耗)":stockResult.getLockQty()==1?"(已锁定"+(rs.getString("busLockRemark")!=null?rs.getString("busLockRemark"):"")+")":""));
                    desc.add(item);
                    item = new HashMap<>();
                    item.put("label", "品规");
                    item.put("value", stockResult.getMaterialSpec());
                    desc.add(item);
                    item = new HashMap<>();
                    item.put("label", "批号效期");
                    item.put("value", stockResult.getLot() + ";" + stockResult.getExp() + ";" + stockResult.getRfid());
                    desc.add(item);
                    stockResult.setDesc(desc);
                    stockResults.add(stockResult);
                }
            }
            conn.close();

        } catch (Exception e) {
            logger.error("getStockBySN:"+e.toString());
        }
        System.out.println(stockResults);
        return stockResults;
    }

    @Override
    public Integer getStockCountByStationCode(String stationCode) {
        String sql = "select COUNT(*) as count from view_fsd_stock where   StockQty>0  and StorageCode ='" + stationCode + "'";
        try {
            ResultSet rs = executeQueryBySql(sql);
            while (rs.next()) {
                return rs.getInt("count");
            }
        }catch (Exception e){
            logger.error("getStockCountByStationCode:"+e.toString());
        }
        return 0;
    }

    @Override
    public List<StockResult> getStockByStationCode(String stationCode) {
        String sql = "select * from view_fsd_stock where   StockQty>0  and StorageCode ='" + stationCode + "'";
        return getStockBySql(sql);
    }

    @Override
    public List<StockResult> getStockByListRfid(List<String> rfids) {
        String sql = "select * from view_fsd_stock where RFID in ('" + StringUtils.join(rfids, "','") + "')";
        List<StockResult> list = getStockBySql(sql);
        for (int i = 0; i < list.size(); i++) {//.stream().filter((StockResult s)->s.getStockQty()==0).collect(Collectors.toList())
            StockResult stockResult = list.get(i);
//            if (stockResult.getStockQty() == 0)
//                stockResult.setMaterialName(stockResult.getMaterialName() + "(已消耗)");
//            if (stockResult.getLockQty() == 1)
//                stockResult.setMaterialName(stockResult.getMaterialName() + "(已锁定)");
//            list.set(i,stockResult);
            rfids.remove(stockResult.getRfid());
        }
        rfids.forEach(rfid->{
            StockResult stockResult = new StockResult();
            stockResult.setId(list.size()+1);
            stockResult.setMaterialCode("");
            stockResult.setMaterialName("未识别的标签");
            stockResult.setPy(PinYinUtils.getAlpha("未识别的标签"));
            stockResult.setMaterialSpec("");
            stockResult.setUnit("");
            stockResult.setType("");
            stockResult.setPackageUnit("");
            stockResult.setPackageQty(0);
            stockResult.setRfid(rfid);
            stockResult.setLot("");
            stockResult.setExp("");
            stockResult.setStockQty(0);
            stockResult.setLockQty(0);
            stockResult.setStorageCode("");
            stockResult.setInactive(true);
            List<Map<String, String>> desc = new ArrayList<>();
            Map<String, String> item = new HashMap<>();
            item.put("label", "品名");
            item.put("value", stockResult.getMaterialName());
            desc.add(item);
            item = new HashMap<>();
            item.put("label", "品规");
            item.put("value", stockResult.getMaterialSpec());
            desc.add(item);
            item = new HashMap<>();
            item.put("label", "批号效期");
            item.put("value", stockResult.getLot() + ";" + stockResult.getExp() + ";" + stockResult.getRfid());
            desc.add(item);
            stockResult.setDesc(desc);
            list.add(stockResult);
        });
        return list;
    }

    @Override
    public List<StockResult> getStockByListSn(List<String> sns) {
        String sql = "select * from view_fsd_lowstock where sn in ('" + StringUtils.join(sns, "','") + "')";
        List<StockResult> list = getStockBySqlSN(sql);
        for (int i = 0; i < list.size(); i++) {//.stream().filter((StockResult s)->s.getStockQty()==0).collect(Collectors.toList())
            StockResult stockResult = list.get(i);
//            if (stockResult.getStockQty() == 0)
//                stockResult.setMaterialName(stockResult.getMaterialName() + "(已消耗)");
//            if (stockResult.getLockQty() == 1)
//                stockResult.setMaterialName(stockResult.getMaterialName() + "(已锁定)");
//            list.set(i,stockResult);
            sns.remove(stockResult.getSn());
        }
        sns.forEach(sn->{
            StockResult stockResult = new StockResult();
            stockResult.setId(list.size()+1);
            stockResult.setMaterialCode("");
            stockResult.setMaterialName("未识别的标签");
            stockResult.setPy(PinYinUtils.getAlpha("未识别的标签"));
            stockResult.setMaterialSpec("");
            stockResult.setUnit("");
            stockResult.setType("");
            stockResult.setPackageUnit("");
            stockResult.setPackageQty(0);
            stockResult.setRfid("");
            stockResult.setSn(sn);
            stockResult.setLot("");
            stockResult.setExp("");
            stockResult.setStockQty(0);
            stockResult.setLockQty(0);
            stockResult.setStorageCode("");
            stockResult.setInactive(true);
            List<Map<String, String>> desc = new ArrayList<>();
            Map<String, String> item = new HashMap<>();
            item.put("label", "品名");
            item.put("value", stockResult.getMaterialName());
            desc.add(item);
            item = new HashMap<>();
            item.put("label", "品规");
            item.put("value", stockResult.getMaterialSpec());
            desc.add(item);
            item = new HashMap<>();
            item.put("label", "批号效期");
            item.put("value", stockResult.getLot() + ";" + stockResult.getExp() + ";" + stockResult.getRfid());
            desc.add(item);
            stockResult.setDesc(desc);
            list.add(stockResult);
        });
        return list;
    }

    @Override
    public RestResult wiNotify(String stationCode, String WiType, List<Map<String, String>> rfids, String opApiVersion, String opFsdType, String opNumber) {
        Map<String, Object> data = new HashMap<>();
        data.put("OpStorageCode",stationCode);
        data.put("OpApiVersion",opApiVersion);
        data.put("OpFsdType",opFsdType);
        data.put("OpGUID", UUID.randomUUID().toString());
        data.put("OpNumber",opNumber);
        data.put("WiType",WiType);
        data.put("RFIDS",rfids);
        HttpEntity httpEntity = buildHttpEntity(data);
        String url = buildUrl(SPDServiceUrl.WI_NOTIFY);
        ResponseEntity<RestResult> response = restTemplate.exchange(url, HttpMethod.POST, httpEntity, RestResult.class);
        RestResult result = response.getBody();
        FsdLog log = FsdLog.getLog(WiType,opApiVersion,"WI_NOTIFY",opNumber,result.getMsg(),httpEntity.toString());
        fsdLogService.save(log);
        if (!result.isSuccess()) {
            throw new BusinessException(result.getMsg()+result.getData());
        }
        return result;
    }

    @Override
    public String woNotify(String stationCode, String woType, String OperationRoomCode, List<Map<String, String>> rfids, String opApiVersion, String opFsdType, String opNumber) {
        Map<String, Object> data = new HashMap<>();
        data.put("OpStorageCode",stationCode);
        data.put("OpApiVersion",opApiVersion);
        data.put("OpFsdType",opFsdType);
        data.put("OperationRoomCode",OperationRoomCode);
        data.put("OpGUID", UUID.randomUUID().toString());
        data.put("OpNumber",opNumber);
        data.put("WoType",woType);
        data.put("RFIDS",rfids);
        HttpEntity httpEntity = buildHttpEntity(data);
        String url = buildUrl(SPDServiceUrl.WO_NOTIFY);
        ResponseEntity<RestResult> response = restTemplate.exchange(url, HttpMethod.POST, httpEntity, RestResult.class);
        RestResult result = response.getBody();
        FsdLog log = FsdLog.getLog(woType,opApiVersion,"WO_NOTIFY",opNumber,result.getMsg(),httpEntity.toString());
        fsdLogService.save(log);
        if (!result.isSuccess()) {
            throw new BusinessException(result.getMsg());
        }
        return JSON.toJSONString(result);
    }

    @Override
    public String consume(String stationCode, List<Map<String, String>> rfids, String opApiVersion, String opFsdType, String opNumber) {
        Map<String, Object> data = new HashMap<>();
        data.put("OpStorageCode",stationCode);
        data.put("OpApiVersion",opApiVersion);
        data.put("OpFsdType",opFsdType);
        data.put("OpGUID", UUID.randomUUID().toString());
        data.put("OpNumber",opNumber);
        data.put("RFIDS",rfids);
        HttpEntity httpEntity = buildHttpEntity(data);
        String url = buildUrl(SPDServiceUrl.CONSUME);
        ResponseEntity<RestResult> response = restTemplate.exchange(url, HttpMethod.POST, httpEntity, RestResult.class);
        RestResult result = response.getBody();
        FsdLog log = FsdLog.getLog("CONSUME",opApiVersion,"CONSUME",opNumber,result.getMsg(),httpEntity.toString());
        fsdLogService.save(log);
        if (!result.isSuccess()) {
            throw new BusinessException(result.getMsg());
        }
        return JSON.toJSONString(result);
    }

    @Override
    public StorageResult getStorageByStorageCode(String stationCode) {
        String sql = "select * from view_fsd_warehouses where StorageCode ='" + stationCode + "'";
        try {
            ResultSet rs = executeQueryBySql(sql);
            System.out.println(sql);
            while (rs.next()) {
                StorageResult storageResult = new StorageResult();
                storageResult.setStorageCode(rs.getString("StorageCode"));
                storageResult.setStorageName(rs.getString("StorageName"));
                storageResult.setParentStorageCode(rs.getString("ParentStorageCode"));
                storageResult.setParentStorageName(rs.getString("ParentStorageName"));
                return storageResult;
            }
        }catch (Exception e){

        }
        return null;
    }

    @Override
    public Map<String,List<ReportResult>> getReportDate(String stationCode,String sheetTab) {
        Map<String, List<ReportResult>> reportResultList = new HashMap<>();
        stationCode = "'" + stationCode + "'";
        switch (sheetTab) {
            case "sheet1"://库存
//                reportResultList.put("subSheet1", getReportSubSeet("SELECT * FROM view_fsd_stock vfs LEFT JOIN ( SELECT RFID FROM fsd_stock WHERE station_code = " + stationCode + " ) b ON vfs.rfid = b.RFID WHERE StockQty > 0  AND Inactive IS FALSE  AND vfs.StorageCode = " + stationCode + "  AND b.rfid IS NULL "));
                reportResultList.put("subSheet1", getReportSubSeet("SELECT vfs.rfid,MaterialName,MaterialSpec,Lot,EXP, fu.user_name, r.change_time, CONCAT( '(', ( CASE WHEN busLockRemark IS NOT NULL THEN CONCAT( '已锁定', busLockRemark, ',' ) WHEN user_name IS NULL THEN '无扫描记录' ELSE CONCAT( user_name, '于', DATE_FORMAT(change_time,'%c-%d'), '取出' ) END ), ')' ) AS remarks FROM view_fsd_stock vfs LEFT JOIN fsd_stock s ON vfs.rfid = s.rfid AND vfs.StorageCode = s.station_code LEFT JOIN ( SELECT fr.rfid, user_id, change_type, fr.change_time, fr.station_code FROM fsd_record fr JOIN ( SELECT max( change_time ) AS change_time, rfid,station_code FROM fsd_record gfr WHERE gfr.change_type = 0 and rfid  in (select rfid From view_fsd_stock where StockQty > 0 and StorageCode = "+stationCode+" and Inactive IS FALSE ) GROUP BY gfr.rfid,gfr.station_code ) jfr ON fr.rfid = jfr.rfid AND fr.station_code = jfr.station_code AND fr.change_time = jfr.change_time ) r ON vfs.rfid = r.rfid AND vfs.RFID = r.rfid AND r.station_code = vfs.StorageCode LEFT JOIN fsd_user fu ON r.user_id = fu.id WHERE vfs.StockQty > 0 AND vfs.Inactive IS FALSE AND s.station_code IS NULL AND StorageCode = " + stationCode + " ORDER BY r.change_time DESC"));
//                reportResultList.put("subSheet2", getReportSubSeet("SELECT * FROM fsd_stock fs LEFT JOIN ( SELECT RFID FROM view_fsd_stock WHERE ( StockQty > 0 AND Inactive IS FALSE ) AND StorageCode = " + stationCode + " ) b ON fs.rfid = b.RFID WHERE fs.station_code = " + stationCode + "  AND b.rfid IS NULL "));
                reportResultList.put("subSheet2", getReportSubSeet("SELECT * FROM ( SELECT s.rfid, IFNULL( fs.MaterialName, '未绑定的标签' ) AS MaterialName, IFNULL( fs.MaterialSpec, '未绑定的标签' ) AS MaterialSpec, IFNULL( fs.Lot, '' ) AS Lot, IFNULL( fs.EXP, '' ) AS EXP, CONCAT( '(', ( CASE WHEN fs.MaterialName IS NULL THEN '未绑定的标签' WHEN vfs.Inactive IS TRUE THEN '耗材已禁用' WHEN vfs.StockQty = 0 THEN '已消耗' WHEN fs.StorageCode = s.station_code THEN NULL ELSE CONCAT( '归属于', fs.StorageCode ) END ), ')' ) AS remarks FROM fsd_stock s LEFT JOIN view_fsd_stock vfs ON vfs.rfid = s.rfid AND vfs.StorageCode = s.station_code LEFT JOIN view_fsd_stock fs ON s.rfid = fs.rfid WHERE station_code = " + stationCode + " ) result WHERE remarks IS NOT NULL"));
                reportResultList.put("subSheet3", getReportSubSeet("SELECT vs.* FROM fsd_stock s JOIN view_fsd_stock vs ON s.rfid = vs.rfid AND s.station_code = vs.StorageCode AND vs.StockQty > 0 AND vs.Inactive IS FALSE AND s.station_code = " + stationCode));
                break;
            case "sheet2"://效期
                reportResultList.put("subSheet1", getReportSubSeet("SELECT rfid,MaterialName,MaterialSpec,Lot,EXP FROM view_fsd_stock WHERE StockQty > 0 AND EXP <= CURDATE( ) AND StorageCode = " + stationCode));
                reportResultList.put("subSheet2", getReportSubSeet("SELECT rfid,MaterialName,MaterialSpec,Lot,EXP FROM view_fsd_stock WHERE StockQty > 0  AND DATE_SUB( EXP, INTERVAL 30 DAY ) <= CURDATE( ) AND EXP > CURDATE( )  AND StorageCode = " + stationCode));
                reportResultList.put("subSheet3", getReportSubSeet("SELECT rfid,MaterialName,MaterialSpec,Lot,EXP FROM view_fsd_stock WHERE StockQty > 0  AND DATE_SUB( EXP, INTERVAL 60 DAY ) <= CURDATE( ) AND DATE_SUB( EXP, INTERVAL 30 DAY ) > CURDATE( )  AND StorageCode = " + stationCode));
                break;
            case "sheet3"://操作
                reportResultList.put("subSheet1", getReportSubSeet("SELECT r.rfid, r.change_time AS ChangeTime, CASE r.change_type WHEN '1' THEN '放入' ELSE '取出' END AS Type, u.user_name AS UserName, u.job_number AS JobNumber , CASE WHEN vfs.MaterialName IS NOT NULL THEN vfs.MaterialName WHEN vfc.rfid IS NOT NULL THEN '已消耗' ELSE '无效标签' END AS MaterialName  FROM fsd_record r JOIN fsd_user u ON r.user_id = u.id JOIN ( SELECT rfid, max( change_time ) AS ChangeTime ,station_code FROM fsd_record WHERE station_code =" + stationCode + " AND date_format( change_time, '%Y-%m-%d' ) = CURDATE( ) GROUP BY rfid ,station_code ) gb ON r.rfid = gb.rfid AND r.station_code = gb.station_code AND r.change_time = gb.ChangeTime left JOIN view_fsd_stock vfs ON r.rfid = vfs.rfid LEFT JOIN view_fsd_consume vfc ON vfc.rfid = r.rfid ORDER BY r.change_time DESC, r.change_type DESC"));
                reportResultList.put("subSheet2", getReportSubSeet("SELECT r.rfid, r.change_time AS ChangeTime, CASE r.change_type WHEN '1' THEN '放入' ELSE '取出' END AS Type, u.user_name AS UserName, u.job_number AS JobNumber , CASE WHEN vfs.MaterialName IS NOT NULL THEN vfs.MaterialName WHEN vfc.rfid IS NOT NULL THEN '已消耗' ELSE '无效标签' END AS MaterialName  FROM fsd_record r JOIN fsd_user u ON r.user_id = u.id JOIN ( SELECT rfid, max( change_time ) AS ChangeTime ,station_code FROM fsd_record WHERE station_code =" + stationCode + " AND date_format( change_time, '%Y-%m-%d' ) = DATE_SUB( CURDATE( ), INTERVAL 1 DAY )  GROUP BY rfid ,station_code ) gb ON r.rfid = gb.rfid AND r.station_code = gb.station_code AND r.change_time = gb.ChangeTime left JOIN view_fsd_stock vfs ON r.rfid = vfs.rfid LEFT JOIN view_fsd_consume vfc ON vfc.rfid = r.rfid ORDER BY r.change_time DESC, r.change_type DESC"));
                reportResultList.put("subSheet3", getReportSubSeet("SELECT r.rfid, r.change_time AS ChangeTime, CASE r.change_type WHEN '1' THEN '放入' ELSE '取出' END AS Type, u.user_name AS UserName, u.job_number AS JobNumber , CASE WHEN vfs.MaterialName IS NOT NULL THEN vfs.MaterialName WHEN vfc.rfid IS NOT NULL THEN '已消耗' ELSE '无效标签' END AS MaterialName  FROM fsd_record r JOIN fsd_user u ON r.user_id = u.id JOIN ( SELECT rfid, max( change_time ) AS ChangeTime ,station_code FROM fsd_record WHERE station_code =" + stationCode + " AND date_format( change_time, '%Y-%m-%d' ) = DATE_SUB( CURDATE( ), INTERVAL 2 DAY )  GROUP BY rfid ,station_code ) gb ON r.rfid = gb.rfid AND r.station_code = gb.station_code AND r.change_time = gb.ChangeTime left JOIN view_fsd_stock vfs ON r.rfid = vfs.rfid LEFT JOIN view_fsd_consume vfc ON vfc.rfid = r.rfid ORDER BY r.change_time DESC, r.change_type DESC"));
                break;
            case "sheet4"://消耗
                reportResultList.put("subSheet1", getReportSubSeet("SELECT vfc.rfid, vfc.ConsumeDate, vfc.ConsumeQty, vfs.MaterialName, vfs.MaterialSpec, vfs.Lot, vfs.Exp FROM view_fsd_consume vfc JOIN view_fsd_stock vfs ON vfc.rfid = vfs.rfid JOIN ( SELECT rfid, max( ConsumeDate ) AS ConsumeDate FROM view_fsd_consume WHERE StorageCode =" + stationCode + " AND ConsumeQty = 1 AND date_format( ConsumeDate, '%Y-%m-%d' ) = CURDATE( ) GROUP BY rfid, StorageCode ) gbvfc ON vfs.rfid = gbvfc.rfid AND vfc.ConsumeDate = gbvfc.ConsumeDate order by ConsumeDate desc"));
                reportResultList.put("subSheet2", getReportSubSeet("SELECT vfc.rfid, vfc.ConsumeDate, vfc.ConsumeQty, vfs.MaterialName, vfs.MaterialSpec, vfs.Lot, vfs.Exp FROM view_fsd_consume vfc JOIN view_fsd_stock vfs ON vfc.rfid = vfs.rfid JOIN ( SELECT rfid, max( ConsumeDate ) AS ConsumeDate FROM view_fsd_consume WHERE StorageCode = " + stationCode + " AND ConsumeQty = 1 AND date_format( ConsumeDate, '%Y-%m-%d' ) = DATE_SUB( CURDATE( ), INTERVAL 1 DAY ) GROUP BY rfid, StorageCode ) gbvfc ON vfs.rfid = gbvfc.rfid AND vfc.ConsumeDate = gbvfc.ConsumeDate order by ConsumeDate desc"));
                reportResultList.put("subSheet3", getReportSubSeet("SELECT vfc.rfid, vfc.ConsumeDate, vfc.ConsumeQty, vfs.MaterialName, vfs.MaterialSpec, vfs.Lot, vfs.Exp FROM view_fsd_consume vfc JOIN view_fsd_stock vfs ON vfc.rfid = vfs.rfid JOIN ( SELECT rfid, max( ConsumeDate ) AS ConsumeDate FROM view_fsd_consume WHERE StorageCode = " + stationCode + " AND ConsumeQty = 1 AND date_format( ConsumeDate, '%Y-%m-%d' ) = DATE_SUB( CURDATE( ), INTERVAL 2 DAY ) GROUP BY rfid, StorageCode ) gbvfc ON vfs.rfid = gbvfc.rfid AND vfc.ConsumeDate = gbvfc.ConsumeDate order by ConsumeDate desc"));
                break;
        }
        return reportResultList;
    }
    private List<ReportResult> getReportSubSeet(String sql) {
        List<ReportResult> reportResultList = new ArrayList<>();
        try {
            ResultSet rs = executeQueryBySql(sql);
            System.out.println(sql);
            while (rs.next()) {
                ReportResult reportResult = new ReportResult();
                reportResult.setRfid(rs.getString("rfid"));
                reportResult.setMaterialName(isExistColumn(rs,"MaterialName")?rs.getString("MaterialName"):"未绑定的标签");
                reportResult.setMaterialSpec(isExistColumn(rs,"MaterialSpec")?rs.getString("MaterialSpec"):"");
                reportResult.setLot(isExistColumn(rs,"Lot")?rs.getString("Lot"):"");
                reportResult.setExp(isExistColumn(rs,"Exp")?rs.getString("Exp"):"");
                reportResult.setQty(isExistColumn(rs,"StockQty")?rs.getString("StockQty"):"");
                reportResult.setUserName(isExistColumn(rs,"UserName")?rs.getString("UserName"):"");
                reportResult.setJobNumber(isExistColumn(rs,"JobNumber")?rs.getString("JobNumber"):"");
                reportResult.setOperationType(isExistColumn(rs,"Type")?rs.getString("Type"):"");
                reportResult.setOperationTime(isExistColumn(rs,"ChangeTime")?rs.getString("ChangeTime"):"");
                reportResult.setRemarks(isExistColumn(rs,"remarks")?rs.getString("remarks"):"");
                reportResultList.add(reportResult);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reportResultList;
    }

    /**
     * 判断查询结果集中是否存在某列
     * @param rs 查询结果集
     * @param columnName 列名
     * @return true 存在; false 不存在
     */
    public boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0 ) {
                return true;
            }
        }
        catch (SQLException e) {
            return false;
        }
        return false;
    }

    @Override
    public List<StorageResult> getBindStationCode() {
        List<StorageResult> storageResults = new ArrayList<>();
        String sql = "select * from view_fsd_warehouses";
        try {
            ResultSet rs = executeQueryBySql(sql);
            System.out.println(sql);
            while (rs.next()) {
                StorageResult storageResult = new StorageResult();
                storageResult.setStorageCode(rs.getString("StorageCode"));
                storageResult.setStorageName(rs.getString("StorageName"));
                storageResult.setParentStorageCode(rs.getString("ParentStorageCode")+"");
                storageResult.setParentStorageName(rs.getString("ParentStorageName")+"");
                storageResults.add(storageResult);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return storageResults;
    }

    @Override
    public List<OperationResult> getOperation() {
        List<OperationResult> operationResults = new ArrayList<>();
        String sql = "select * from view_fsd_oprts";
        try {
            ResultSet rs = executeQueryBySql(sql);
            System.out.println(sql);
            while (rs.next()) {
                OperationResult operationResult = new OperationResult();
                operationResult.setOpNo(rs.getString("code"));
                operationResult.setOpName(rs.getString("name"));
                operationResult.setDoctorCode(rs.getString("DoctorCode"));
                operationResult.setDoctorName(rs.getString("DoctorName"));
                operationResult.setPatientCode(rs.getString("PatientCode"));
                operationResult.setPatientName(rs.getString("PatientName"));
                operationResult.setOpRoomCode(rs.getString("opRoomCode"));
                operationResult.setOpRoomName(rs.getString("opRoomName"));
                operationResult.setOpTime(rs.getString("opTime"));
                operationResults.add(operationResult);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return operationResults;
    }

    @Override
    public List<OperationRoomResult> getOperationRoom() {
        List<OperationRoomResult> operationRoomResults = new ArrayList<>();
        String sql = "select * from view_fsd_depts where bus_type = 2";
        try {
            ResultSet rs = executeQueryBySql(sql);
            while (rs.next()) {
                OperationRoomResult operationRoomResult = new OperationRoomResult();
                operationRoomResult.setOpRoomCode(rs.getString("code"));
                operationRoomResult.setOpRoomName(rs.getString("name"));
                operationRoomResults.add(operationRoomResult);
            }
        }catch (Exception e){
            logger.error("getOperationRoom:"+e.toString());
            e.printStackTrace();
        }
        return operationRoomResults;
    }

    @Override
    public List<FsdUser> getUsers() {
        List<FsdUser> users = new ArrayList<>();
        String sql = "select * from view_fsd_users where Number is not null and UerName is not null and Inactive=0";
        try {
            ResultSet rs = executeQueryBySql(sql);
            while (rs.next()) {
                FsdUser fsdUser = new FsdUser();
                fsdUser.setJobNumber(rs.getString("Number"));
                fsdUser.setUserName(rs.getString("UerName"));
                fsdUser.setUserNamePy(PinYinUtils.getAlpha(fsdUser.getUserName()));
                fsdUser.setPassword("111111");
                fsdUser.setJob("用户");
                fsdUser.setCardId(rs.getString("Number"));
//                SaveUserDTO userDTO = new SaveUserDTO(rs.getString("UerName"),rs.getString("Number"),"111111","用户",rs.getString("Number"),"",rs.getBoolean("Inactive"),1);
                users.add(fsdUser);
            }
        }catch (Exception e){
            logger.error("getUsers:"+e.toString());
            e.printStackTrace();
        }
        return users;
    }

    @Override
    public String scanNotify(String stationCode, List<String> rfids, String opApiVersion, String opFsdType, String opNumber) {
        Map<String, Object> data = new HashMap<>();
        data.put("OpStorageCode",stationCode);
        data.put("OpApiVersion",opApiVersion);
        data.put("OpFsdType",opFsdType);
        data.put("OpGUID", UUID.randomUUID().toString());
        data.put("OpNumber",opNumber);
        data.put("RFIDS",rfids);
        HttpEntity httpEntity = buildHttpEntity(data);
        String url = buildUrl(SPDServiceUrl.SCANNOTIFY);
        ResponseEntity<RestResult> response = restTemplate.exchange(url, HttpMethod.POST, httpEntity, RestResult.class);
        RestResult result = response.getBody();
        FsdLog log = FsdLog.getLog("scanNotify",opApiVersion,"scanNotify",opNumber,result.getMsg(),httpEntity.toString());
        fsdLogService.save(log);
//        if (!result.isSuccess()) {
////            throw new BusinessException(result.getMsg());
////        }
        return JSON.toJSONString(result);
    }

    public ResultSet executeQueryBySql(String sql){
        try {
            Class.forName(driverClassName);
            Connection conn = DriverManager.getConnection(url, username, UtilDatabase.getPassword(username));
            Statement stmt = conn.createStatement();
            ResultSet hasResultSet = stmt.executeQuery(sql);
            if (hasResultSet != null) {
                return stmt.getResultSet();
            }
            conn.close();
        } catch (Exception e) {
            logger.error("executeQueryBySql:"+e.toString());
            e.printStackTrace();

        }
        return null;
    }

    /**
     * 构建接口地址
     *
     * @param apiUrl
     * @return
     */
    private String buildUrl(String apiUrl) {
        String host = spdProperties.getHost();
        int port = spdProperties.getPort();

        if (StringUtils.isBlank(host)) {
            throw new BusinessException("未配置SPD系统地址; 请确认配置文件中  system.spd.* 是否配置正确");
        }

        if (apiUrl.trim().startsWith("/")) {
            apiUrl = apiUrl.trim().substring(1);
        }

        return String.format("http://%s:%d/%s", host.trim(), port, apiUrl);
    }

    private <T> HttpEntity buildHttpEntity(@Nullable T data) {
        String token = spdProperties.getToken();//添加一个固定的token用于验证

        HttpHeaders headers = new HttpHeaders();
        headers.add("X-Token", token);
        headers.add("Content-Type", "application/json");
        headers.add("User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.99 Safari/537.36");
        return new HttpEntity(data, headers);
    }
}
